﻿CREATE PROCEDURE dbo.SP_Members_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @Birthdate DateTime   =  NULL 
, @ConfessionFather NChar (10)  =  NULL 
, @CountryId Int   =  NULL 
, @CreationDate DateTime   =  NULL 
, @DisplayName NVarChar (256)  =  NULL 
, @Email NVarChar (256)  =  NULL 
, @FirstName NVarChar (256)  =  NULL 
, @Gender TinyInt   =  NULL 
, @ImagePath NVarChar (50)  =  NULL 
, @IsActive Bit   =  NULL 
, @LastName NVarChar (256)  =  NULL 
, @LastUpdateDate DateTime   =  NULL 
, @MemberId Int   =  NULL 
, @NationalityId Int   =  NULL 
, @Password NVarChar (500)  =  NULL 
	AS
			WITH CTE_Members AS
			( 
			SELECT
 							Birthdate,
								ConfessionFather,
								CountryId,
								CreationDate,
								DisplayName,
								Email,
								FirstName,
								Gender,
								ImagePath,
								IsActive,
								LastName,
								LastUpdateDate,
								MemberId,
								NationalityId,
								Password,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume='Birthdate' THEN Birthdate
						 
						WHEN @OrderColume='ConfessionFather' THEN ConfessionFather
												WHEN @OrderColume='CountryId' THEN CountryId
												WHEN @OrderColume='CreationDate' THEN CreationDate
						 
						WHEN @OrderColume='DisplayName' THEN DisplayName
						 
						WHEN @OrderColume='Email' THEN Email
						 
						WHEN @OrderColume='FirstName' THEN FirstName
												WHEN @OrderColume='Gender' THEN Gender
						 
						WHEN @OrderColume='ImagePath' THEN ImagePath
												WHEN @OrderColume='IsActive' THEN IsActive
						 
						WHEN @OrderColume='LastName' THEN LastName
												WHEN @OrderColume='LastUpdateDate' THEN LastUpdateDate
												WHEN @OrderColume='MemberId' THEN MemberId
												WHEN @OrderColume='NationalityId' THEN NationalityId
						 
						WHEN @OrderColume='Password' THEN Password
									ELSE 
							    MemberId
					        	END
			) AS "RowNumber"
  			FROM Members 
			where 
								(Birthdate = @Birthdate OR @Birthdate Is Null)
										AND(ConfessionFather LIKE @ConfessionFather OR @ConfessionFather Is Null)
										AND(CountryId = @CountryId OR @CountryId Is Null)
										AND(CreationDate = @CreationDate OR @CreationDate Is Null)
										AND(DisplayName LIKE @DisplayName OR @DisplayName Is Null)
										AND(Email LIKE @Email OR @Email Is Null)
										AND(FirstName LIKE @FirstName OR @FirstName Is Null)
										AND(Gender = @Gender OR @Gender Is Null)
										AND(ImagePath LIKE @ImagePath OR @ImagePath Is Null)
										AND(IsActive = @IsActive OR @IsActive Is Null)
										AND(LastName LIKE @LastName OR @LastName Is Null)
										AND(LastUpdateDate = @LastUpdateDate OR @LastUpdateDate Is Null)
										AND(MemberId = @MemberId OR @MemberId Is Null)
										AND(NationalityId = @NationalityId OR @NationalityId Is Null)
										AND(Password LIKE @Password OR @Password Is Null)
								)
			SELECT  *
			FROM CTE_Members
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			